package com.dagene.weixin.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.dagene.weixin.entity.ChatMsg;


public interface ChatMsgDao extends CrudRepository<ChatMsg, Long> {	
	
	ChatMsg findByMsgid(long msgid);
			
	List<ChatMsg> findByMatchnumberOrderByMsgidDesc(int matchnumber);
	
	List<ChatMsg> findByWorker(String worker);
	
	List<ChatMsg> findByOpenid(String openid);
	
	@Query("select s from ChatMsg s group by openid")
	List<ChatMsg> findGroupByOpenid();
	
	//@Query("select s from ChatMsg s where worker=?1 and datediff(time,?2)=0")
	@Query("select s from ChatMsg s where worker=?1 and time>=?2 and time<=?3")
	List<ChatMsg> findByWorkerAndTime(String worker, Date time1, Date time2);
	
	@Query("select s from ChatMsg s where openid=?1 and time>=?2 and time<=?3")
	List<ChatMsg> findByOpenidAndTime(String openid, Date time1, Date time2);
	
	@Query("select s from ChatMsg s where time>=?1 and time<=?2")
	List<ChatMsg> findByTime(Date time1, Date time2);
	
	@Query("select count(*) from ChatMsg s where worker=?1 and message='401' and time>=?2 and time<=?3")
	long findAcountByWorker(String openid, Date time1, Date time2);
	
	@Query("select count(*) from ChatMsg s where message='401' and time>=?1 and time<=?2")
	long findAcountByTime(Date time1, Date time2);
}
